﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using AChance.Dal.Func;
using AChance.Func;

namespace AChance.Bll.Func
{
    public static class DalQuery
    {
        /// <summary>
        /// 查询加工
        /// </summary>
        public class Proc
        {
            /// <summary>
            /// 类型
            /// </summary>
            public enum Type
            {
                /// <summary>
                /// 普通
                /// </summary>
                Normal,
                /// <summary>
                /// 分页
                /// </summary>
                Page,
                /// <summary>
                /// 聚集
                /// </summary>
                Gather
            }
            /// <summary>
            /// 指定选择条件
            /// </summary>
            public List<SelectProc.Condition> Select { get; set; } = new List<SelectProc.Condition>();
            /// <summary>
            /// 指定选择求总数条件
            /// </summary>
            public List<SelectProc.Condition> SelectSum { get; set; } = new List<SelectProc.Condition>();
            /// <summary>
            /// 指定判断条件
            /// </summary>
            public List<WhereProc.Condition> Where { get; set; } = new List<WhereProc.Condition>();
            /// <summary>
            /// 指定排序条件
            /// </summary>
            public List<OrderProc.Condition> Order { get; set; } = new List<OrderProc.Condition>();
            /// <summary>
            /// 指定获取该页数据，0表示不分页获取  
            /// </summary>                         
            public int PageIndex { get; set; } = 0;
            /// <summary>
            /// 指定每页记录数，0表示不分页获取，该属性只有在<see cref="PageIndex"/>大于0的情况下才有效
            /// </summary>
            public int PageSize { get; set; } = 0;
            /// <summary>
            /// 获取在融合所有加工条件后的完整Sql
            /// </summary>
            public string GetSql(DalSqlBase.Type sqlType, string coreSql, Type procType = Type.Normal)
            {
                const string ORDER_BY = " ORDER BY ";
                string sql = coreSql;
                string selectSql = GetSelectSql();
                string selectSumSql = GetSelectSumSql(sqlType);
                string whereSql = GetWhereSql();
                string orderSql = GetOrderSql();
                if (coreSql.ContainsIgnoreCase(ORDER_BY))
                {
                    string coreOrderSql = coreSql.Substring(coreSql.LastIndexOf(ORDER_BY, StringComparison.OrdinalIgnoreCase));
                    if (coreOrderSql.Split("(").Length == coreOrderSql.Split(")").Length)
                    {
                        sql = coreSql.Substring(0, coreSql.Length - coreOrderSql.Length);
                        orderSql = orderSql.IsNullOrEmpty() ? coreOrderSql.ReplaceIgnoreCase(ORDER_BY, "") : orderSql;
                    }
                }
                switch (procType)
                {
                    case Type.Page:
                        if (PageIndex <= 0 || PageSize <= 0)
                            goto default;
                        switch (sqlType)
                        {
                            default:
                                sql = $"SELECT {(selectSql.IsNullOrEmpty() ? "*" : selectSql)} "
                                         + $"FROM ({sql}) AS TEMP "
                                         + $"{(whereSql.IsNullOrEmpty() ? "" : $"WHERE {whereSql}")} "
                                         + $"ORDER BY {(orderSql.IsNullOrEmpty() ? "(SELECT 0)" : orderSql)} "
                                         + $"OFFSET {(PageIndex - 1) * PageSize} ROWS FETCH NEXT {PageSize} ROWS ONLY ";
                                break;
                        }
                        break;
                    case Type.Gather:
                        sql = $"SELECT COUNT(*) AS {nameof(Result.TotalNumber)} "
                                 + $"{(selectSumSql.IsNullOrEmpty() ? "" : $",{selectSumSql}")} "
                                 + $"FROM ({sql}) AS TEMP "
                                 + $"{(whereSql.IsNullOrEmpty() ? "" : $"WHERE {whereSql}")} ";
                        break;
                    default:
                        sql = $"SELECT {(selectSql.IsNullOrEmpty() ? "*" : selectSql)} "
                                 + $"FROM ({sql}) AS TEMP "
                                 + $"{(whereSql.IsNullOrEmpty() ? "" : $"WHERE {whereSql}")} "
                                 + $"{(orderSql.IsNullOrEmpty() ? "" : $"ORDER BY {orderSql}")} ";
                        break;
                }
                return sql.Trim();
            }
            /// <summary>
            /// 获取指定选择条件对应的Sql
            /// </summary>
            public string GetSelectSql() => SelectProc.ConvertToSql(Select);
            /// <summary>
            /// 获取指定选择求总数条件对应的Sql
            /// </summary>
            public string GetSelectSumSql(DalSqlBase.Type sqlType) => SelectProc.ConvertToSumSql(sqlType, SelectSum);
            /// <summary>
            /// 获取指定判断条件对应的Sql
            /// </summary>
            public string GetWhereSql() => WhereProc.ConvertToSql(Where);
            /// <summary>
            /// 获取指定排序条件对应的Sql
            /// </summary>
            public string GetOrderSql() => OrderProc.ConvertToSql(Order);
        }
        /// <summary>
        /// 查询结果
        /// </summary>
        public class Result
        {
            /// <summary>
            /// 数据列表
            /// </summary>
            public DataTable DataList { get; set; } = new DataTable();
            /// <summary>
            /// 数据总数
            /// </summary>
            public Dictionary<string, object> DataSum { get; set; } = new Dictionary<string, object>();
            /// <summary>
            /// 总页数
            /// </summary>
            public int TotalPage { get; set; } = 0;
            /// <summary>
            /// 总记录数
            /// </summary>
            public int TotalNumber { get; set; } = 0;
            /// <summary>
            /// 实际获取的是该页数据，0表示已经全部获取  
            /// </summary>     
            public int PageIndex { get; set; } = 0;
            /// <summary>
            /// 每页记录数
            /// </summary>
            public int PageSize { get; set; } = 0;
        }

        /// <summary>
        /// 选择条件加工
        /// </summary>
        public static class SelectProc
        {
            public class Condition
            {
                public string Name { get; set; } = "";
                public string Alias { get; set; } = "";
            }
            public static string ConvertToSql(List<Condition> conditions)
            {
                if (conditions.IsNullOrEmpty())
                    return null;
                try
                {
                    string select = "";
                    foreach (Condition condition in conditions)
                    {
                        if (condition.Name.IsNullOrEmpty()
                            || !condition.Name.IsNumOrEn()
                            || (!condition.Alias.IsNullOrEmpty() && !condition.Alias.IsNumOrEn()))
                            throw new Exception();

                        select += $"{(select.IsNullOrEmpty() ? "" : ",")}{condition.Name} {(condition.Alias.IsNullOrEmpty() ? "" : $"AS {condition.Alias}")}";
                    }
                    if (select.Length > 0)
                        return $" {select} ";
                    else
                        return null;
                }
                catch
                {
                    throw new Exception("查询加工中的选择条件转换失败");
                }
            }
            public static string ConvertToSumSql(DalSqlBase.Type sqlType, List<Condition> conditions)
            {
                if (conditions.IsNullOrEmpty())
                    return null;
                try
                {
                    string select = "";
                    foreach (Condition condition in conditions)
                    {
                        if (condition.Name.IsNullOrEmpty()
                            || !condition.Name.IsNumOrEn()
                            || (!condition.Alias.IsNullOrEmpty() && !condition.Alias.IsNumOrEn()))
                            throw new Exception();

                        switch (sqlType)
                        {
                            default:
                                select += $"{(select.IsNullOrEmpty() ? "" : ",")}ISNULL(SUM({condition.Name}),0) AS {(condition.Alias.IsNullOrEmpty() ? condition.Name : condition.Alias)}";
                                break;
                        }
                    }
                    if (select.Length > 0)
                        return $" {select} ";
                    else
                        return null;
                }
                catch
                {
                    throw new Exception("查询加工中的选择求总数条件转换失败");
                }
            }
        }
        /// <summary>
        /// 判断条件加工
        /// </summary>
        public static class WhereProc
        {
            public static class DataType
            {
                public const string STRING = "String";
                public const string INTEGER = "Integer";
                public const string DOUBLE = "Double";
                public const string DATE = "Date";
                //public const string DATE_YEAR_MONTH = "DateYearMonth";
                //public const string PROCESS_STATUS = "PROCESS_STATUS";

                private static List<string> _dataTypes = new List<string>();
                static DataType()
                {
                    foreach (FieldInfo fieldInfo in typeof(DataType).GetFields())
                        if (fieldInfo.FieldType == typeof(string))
                            _dataTypes.Add(((string)fieldInfo.GetValue(null)).ToUpper());
                }
                public static bool Exists(string dataType)
                {
                    return _dataTypes.Contains(dataType.ToUpper());
                }
            }
            public static class JudgeType
            {
                public const string LIKE = "LIKE";
                public const string LEFT_LIKE = "LEFT_LIKE";
                public const string RIGHT_LIKE = "RIGHT_LIKE";
                public const string NOT_LIKE = "NOT_LIKE";
                public const string EQUAL = "=";
                public const string NOT_EQUAL = "<>";
                public const string LARGE = ">";
                public const string LARGE_EQUAL = ">=";
                public const string LITTLE = "<";
                public const string LITTLE_EQUAL = "<=";

                private static List<string> _judgeTypes = new List<string>();
                static JudgeType()
                {
                    foreach (FieldInfo fieldInfo in typeof(JudgeType).GetFields())
                        if (fieldInfo.FieldType == typeof(string))
                            _judgeTypes.Add(((string)fieldInfo.GetValue(null)).ToUpper());
                }
                public static bool Exists(string judgeType)
                {
                    return _judgeTypes.Contains(judgeType.ToUpper());
                }
            }
            public static class LogicType
            {
                public const string AND = "AND";
                public const string OR = "OR";

                private static List<string> _logicTypes = new List<string>();
                static LogicType()
                {
                    foreach (FieldInfo fieldInfo in typeof(LogicType).GetFields())
                        if (fieldInfo.FieldType == typeof(string))
                            _logicTypes.Add(((string)fieldInfo.GetValue(null)).ToUpper());
                }
                public static bool Exists(string logicType)
                {
                    return _logicTypes.Contains(logicType.ToUpper());
                }
            }
            public class Condition
            {
                public string Name { get; set; } = "";
                public string Type { get; set; } = "";
                public string Judge { get; set; } = "";
                public string Value { get; set; } = "";
                public string Logic { get; set; } = "";
            }
            public static string ConvertToSql(List<Condition> conditions)
            {
                if (conditions.IsNullOrEmpty())
                    return null;
                try
                {
                    string where = "";
                    string logic = "";
                    foreach (Condition condition in conditions)
                    {
                        if (condition.Name.IsNullOrEmpty()
                            || !condition.Name.IsNumOrEn()
                            || !DataType.Exists(condition.Type)
                            || !JudgeType.Exists(condition.Judge)
                            || !LogicType.Exists(condition.Logic))
                            throw new Exception();

                        string subWhere = "";
                        if (condition.Type.EqualsIgnoreCase(DataType.INTEGER))
                        {
                            if (int.TryParse(condition.Value, out int intValue))
                                subWhere = $"{condition.Name} {condition.Judge} {condition.Value}";
                            else
                                subWhere = $"{condition.Name} = -87546.2397";
                        }
                        else if (condition.Type.EqualsIgnoreCase(DataType.DOUBLE))
                        {
                            if (double.TryParse(condition.Value, out double dblValue))
                                subWhere = $"{condition.Name} {condition.Judge} {condition.Value}";
                            else
                                subWhere = $"{condition.Name} = -87546.2397";
                        }
                        else
                        {
                            if (condition.Type.EqualsIgnoreCase(DataType.DATE))
                                condition.Value = condition.Value.Replace("-", "");

                            if (condition.Judge.EqualsIgnoreCase(JudgeType.LIKE))
                                subWhere = $"CAST({condition.Name} AS VARCHAR(1000)) LIKE '%'+{condition.Value.ToSqlArg()}+'%'";
                            else if (condition.Judge.EqualsIgnoreCase(JudgeType.LEFT_LIKE))
                                subWhere = $"CAST({condition.Name} AS VARCHAR(1000)) LIKE {condition.Value.ToSqlArg()}+'%'";
                            else if (condition.Judge.EqualsIgnoreCase(JudgeType.RIGHT_LIKE))
                                subWhere = $"CAST({condition.Name} AS VARCHAR(1000)) LIKE '%'+{condition.Value.ToSqlArg()}";
                            else if (condition.Judge.EqualsIgnoreCase(JudgeType.NOT_LIKE))
                                subWhere = $"CAST({condition.Name} AS VARCHAR(1000)) NOT LIKE '%'+{condition.Value.ToSqlArg()}+'%'";
                            else
                                subWhere = $"CAST({condition.Name} AS VARCHAR(1000)) {condition.Judge} {condition.Value.ToSqlArg()}";
                        }
                        where += $"{logic} ({subWhere}) ";
                        logic = condition.Logic;
                    }
                    if (where.Length > 0)
                        return $" ({where}) ";
                    else
                        return null;
                }
                catch
                {
                    throw new Exception("查询加工中的判断条件转换失败");
                }
            }
        }
        /// <summary>
        /// 排序条件加工
        /// </summary>
        public static class OrderProc
        {
            public static class OrderType
            {
                public const string ASC = "ASC";
                public const string DESC = "DESC";

                private static List<string> _orderTypes = new List<string>();
                static OrderType()
                {
                    foreach (FieldInfo fieldInfo in typeof(OrderType).GetFields())
                        if (fieldInfo.FieldType == typeof(string))
                            _orderTypes.Add(((string)fieldInfo.GetValue(null)).ToUpper());
                }
                public static bool Exists(string orderType)
                {
                    return _orderTypes.Contains(orderType.ToUpper());
                }
            }
            public class Condition
            {
                public string Name { get; set; } = "";
                public string Order { get; set; } = "";
            }
            public static string ConvertToSql(List<Condition> conditions)
            {
                if (conditions.IsNullOrEmpty())
                    return null;
                try
                {
                    string order = "";
                    foreach (Condition condition in conditions)
                    {
                        if (condition.Name.IsNullOrEmpty()
                            || !condition.Name.IsNumOrEn()
                            || !OrderType.Exists(condition.Order))
                            throw new Exception();

                        order += $"{(order.IsNullOrEmpty() ? "" : ",")}{condition.Name} {condition.Order}";
                    }
                    if (order.Length > 0)
                        return $" {order} ";
                    else
                        return null;
                }
                catch
                {
                    throw new Exception("查询加工中的排序条件转换失败");
                }
            }
        }
    }
}
